﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class ZkcdtDAL:IZkcdtDAL
    {
        public int Add(ZkcdtInfo dt)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dt.Dtnum),
                DALHelper.MakeInParam(":num", OracleType.VarChar, dt.Num),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dt.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dt.Tianbao),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, dt.Kday)
            };
            return (DALHelper.RunCommand(@"insert into dd.ZKCDT (dtnum, num, banzu, tianbao, kday) values (:dtnum, :num, :banzu, :tianbao, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'))", prams));
        }
        public int Update(string dtnum, decimal kn, decimal kndc, decimal knkl, decimal knzy, decimal knwj, decimal dk, decimal dkdc, decimal dkkl, decimal dkzy, decimal dkwj)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn),
                DALHelper.MakeInParam(":kndc", OracleType.Number, kndc),
                DALHelper.MakeInParam(":knkl", OracleType.Number, knkl),
                DALHelper.MakeInParam(":knzy", OracleType.Number, knzy),
                DALHelper.MakeInParam(":knwj", OracleType.Number, knwj),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk),
                DALHelper.MakeInParam(":dkdc", OracleType.Number, dkdc),
                DALHelper.MakeInParam(":dkkl", OracleType.Number, dkkl),
                DALHelper.MakeInParam(":dkzy", OracleType.Number, dkzy),
                DALHelper.MakeInParam(":dkwj", OracleType.Number, dkwj)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set kn = :kn, kndc = :kndc, knkl = :knkl, knzy = :knzy, knwj = :knwj, dk = :dk, dkdc = :dkdc, dkkl = :dkkl, dkzy = :dkzy, dkwj = :dkwj where dtnum = :dtnum", prams));
        }
        public int UpdateKN(string dtnum, decimal kn)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set kn = :kn where dtnum = :dtnum", prams));
        }
        public int UpdateKNDC(string dtnum, decimal kndc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kndc", OracleType.Number, kndc)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set kndc = :kndc where dtnum = :dtnum", prams));
        }

        public int UpdateKNKL(string dtnum, decimal knkl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knkl", OracleType.Number, knkl)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set knkl = :knkl where dtnum = :dtnum", prams));
        }
        public int UpdateKNZY(string dtnum, decimal knzy)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knzy", OracleType.Number, knzy)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set knzy = :knzy where dtnum = :dtnum", prams));
        }
        public int UpdateKNWJ(string dtnum, decimal knwj)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knwj", OracleType.Number, knwj)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set knwj = :knwj where dtnum = :dtnum", prams));
        }
        public int UpdateDK(string dtnum, decimal dk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set dk = :dk where dtnum = :dtnum", prams));
        }
        public int UpdateDKDC(string dtnum, decimal dkdc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkdc", OracleType.Number, dkdc)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set dkdc = :dkdc where dtnum = :dtnum", prams));
        }
        public int UpdateDKKL(string dtnum, decimal dkkl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkkl", OracleType.Number, dkkl)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set dkkl = :dkkl where dtnum = :dtnum", prams));
        }
        public int UpdateDKZY(string dtnum, decimal dkzy)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkzy", OracleType.Number, dkzy)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set dkzy = :dkzy where dtnum = :dtnum", prams));
        }
        public int UpdateDKWJ(string dtnum, decimal dkwj)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkwj", OracleType.Number, dkwj)
            };
            return (DALHelper.RunCommand(@"update dd.ZKCDT set dkwj = :dkwj where dtnum = :dtnum", prams));
        }
        public int Del(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.ZKCDT where DTNUM = :dtnum", prams));
        }
        public DataSet Select(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.ZKCDT where dtnum = :dtnum", prams, "jl"));
        }
        public DataSet SelectAll()
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select dt.dtnum as dtnum, jg.ku as ku, jg.pinzhong as pinzhong, jg.guige as guige, dt.kday as kday, dt.banzu as banzu, dt.tianbao as tianbao, dt.kn as kn, dt.kndc as kndc, dt.knkl as knkl, dt.knzy as knzy, dt.knwj as knwj, dt.dk as dk, dt.dkdc as dkdc, dt.dkkl as dkkl, dt.dkzy as dkzy, dt.dkwj as dkwj from dd.ZKCDT dt, dd.KC jg where dt.num = jg.num and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jlall"));

        }
        public DataSet SelectPG()
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select num, pinzhong, guige from dd.KC where ku = '板材库' and num not in (select num from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss'))", prams, "pg"));

        }
        public string KuKC(string cday)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(kn) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string JDZB(string cday)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string WJK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkwj + knwj) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string KLY(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkkl + knkl) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string DCL(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkdc + kndc) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string ZYL(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkzy + knzy) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string ZKC(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk + kn) from dd.ZKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
    }
}
